import java.sql.*;
import java.util.Scanner;
public class Main {
    public static Connection conn = null;

    //主函数进行主要功能选择
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 建立数据库连接
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/library?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true";
        String user = "root";
        String password = "Fch20051128";
        conn = DriverManager.getConnection(url, user, password);
        while (true) {
            System.out.print("1.查询图书\n2.借阅图书\n3.添加图书\n4.归还图书\n5.删除图书\n6.修改图书信息\n7.退出\n请选择您要进行的操作: ");
            Scanner sc = new Scanner(System.in);
            int a = sc.nextInt();
            switch (a) {
                case 1:
                    quarybooks();
                    break;
                case 2:
                    borrow();
                    break;
                case 3:
                    addbooks();
                    break;
                case 4:
                    backbooks();
                    break;
                case 5:
                    delbooks();
                    break;
                case 6:
                    updatebooks();
                    break;
                case 7:
                    System.out.println("-----------------感谢使用----------------");
                    System.exit(0);
            }
        }
    }
    //查询功能，选择id查询或名称查询，遍历对比与输入信息相同则输出信息
    public static void quarybooks() throws ClassNotFoundException, SQLException {
   
        String sql = "select * from library.books";
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery(sql);
        Scanner choice = new Scanner(System.in);
        System.out.println("请选择您要查询的方式：\n1.id查询\n2.名称查询\n3.退出");
        int choice1 = choice.nextInt();
        if (choice1 == 1) {
            System.out.println("请输入您想要查询的图书id:");
            int bookid = choice.nextInt();
            while (result.next()) {
                int id = result.getInt(1);
                if (bookid == id) {
                    String name = result.getString(2);
                    String author = result.getString(3);
                    double price = result.getDouble(4);
                    String ifborrowed = result.getString(5);
                    System.out.print("图书id:" + id + "\t");
                    System.out.print("图书名称:" + name + "\t");
                    System.out.print("图书作者:" + author + "\t");
                    System.out.print("图书价格:" + price + "\t");
                    System.out.println("是否在馆:" + ifborrowed + "\t");
                } else
                    continue;
            }
        } else if (choice1 == 2) {
            System.out.println("请输入您想要查询的图书名称:");
            Scanner sc = new Scanner(System.in);
            String name1 = sc.next();
            while (result.next()) {
                int id = result.getInt(1);
                String name = result.getString(2);
                String author = result.getString(3);
                double price = result.getDouble(4);
                String ifborrowed = result.getString(5);
                boolean areEqual = name1.equals(name);
                if (areEqual) {
                    System.out.print("图书id:" + id + "\t");
                    System.out.print("图书名称:" + name + "\t");
                    System.out.print("图书作者:" + author + "\t");
                    System.out.print("图书价格:" + price + "\t");
                    System.out.println("是否在馆:" + ifborrowed + "\t\n");
                } else
                    continue;
            }
        } else {
            System.exit(0);
        }
    }
    //借阅功能，输入id或名称进行查询是否在馆，在馆状态为yes则提供是否确定借阅选择，确定借阅后将图书在馆信息改为no
    public static void borrow() throws ClassNotFoundException, SQLException {
        String sql = "select * from library.books";
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery(sql);
        Scanner choice = new Scanner(System.in);
        System.out.println("请选择您要借阅图书的方式：\n1.id查询\n2.名称查询\n3.退出");
        int choice1 = choice.nextInt();
        if (choice1 == 1) {
            System.out.println("请输入您想要借阅的图书id:");
            int bookid = choice.nextInt();
            while (result.next()) {
                int id = result.getInt(1);
                if (bookid == id) {
                    String name = result.getString(2);
                    String author = result.getString(3);
                    double price = result.getDouble(4);
                    String ifborrow = result.getString(5);
                    System.out.print("图书id:" + id + "\t");
                    System.out.print("图书名称:" + name + "\t");
                    System.out.print("图书作者:" + author + "\t");
                    System.out.print("图书价格:" + price + "\t");
                    System.out.println("是否在馆:" + ifborrow + "\t");
                    if (ifborrow.equals("yes")) {
                        System.out.println("是否确定借阅\n1.是\n2.否");
                        int choice2 = choice.nextInt();
                        if (choice2 == 1) {
                            String Updatesql = String.format("UPDATE books SET ifborrowed ='no'WHERE id ='%s'", id);
                            PreparedStatement updateStatement = conn.prepareStatement(Updatesql);
                            updateStatement.executeUpdate();
                            System.out.println("成功借阅！\n");
                        } else continue;
                    } else
                        System.out.println("图书不在馆，不可借阅！\n");
                } else
                    continue;
            }
        } else if (choice1 == 2) {
            System.out.println("请输入您想要借阅的图书名称:");
            Scanner sc = new Scanner(System.in);
            String name1 = sc.next();
            while (result.next()) {
                int id = result.getInt(1);
                String author = result.getString(3);
                double price = result.getDouble(4);
                String ifborrow = result.getString(5);
                String name = result.getString(2);
                boolean areEqual = name1.equals(name);
                if (areEqual) {
                    System.out.print("图书id:" + id + "\t");
                    System.out.print("图书名称:" + name + "\t");
                    System.out.print("图书作者:" + author + "\t");
                    System.out.print("图书价格:" + price + "\t");
                    System.out.println("是否在馆:" + ifborrow + "\t\n");
                    if (ifborrow.equals("yes")) {
                        System.out.println("是否确定借阅\n1.是\n2.否");
                        int choice2 = choice.nextInt();
                        if (choice2 == 1) {
                            String Updatesql = String.format("UPDATE books SET ifborrowed ='no'WHERE name ='%s'", name1);
                            PreparedStatement updateStatement = conn.prepareStatement(Updatesql);
                            updateStatement.executeUpdate();
                            System.out.println("成功借阅！\n");
                            //更新数据
                        } else
                            continue;
                    } else
                        System.out.println("图书不在馆，不可借阅！\n");
                }
            }
        } else System.exit(0);
    }
    //添加功能，输入图书信息后进行储存并更新数据库
    public static void addbooks() throws SQLException, ClassNotFoundException {
        Statement stmt = conn.createStatement();
        PreparedStatement ps = null;
        Scanner content = new Scanner(System.in);
        System.out.println("请输入图书id：");
        int id = content.nextInt();
        System.out.println("请输入图书名称：");
        String name = content.next();
        System.out.println("请输入图书作者：");
        String author = content.next();
        System.out.println("请输入图书价格：");
        int price = content.nextInt();
        System.out.println("请输入图书在馆状态：");
        String ifborrowed = content.next();
        String sql = "INSERT INTO library.books(id,name,author,price,ifborrowed) VALUES(?,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ps.setString(2, name);
        ps.setString(3, author);
        ps.setInt(4, price);
        ps.setString(5, ifborrowed);
        ps.executeUpdate();
        //stmt.executeUpdate(sql);
        System.out.println("添加成功！");
    }
    //删除功能，通过对比图书名称进行整列删除
    public static void delbooks() throws SQLException, ClassNotFoundException {
        Scanner na = new Scanner(System.in);
        System.out.println("请输入您要删除图书的名称：");
        String bookname = na.next();
        String sql = String.format("delete from books where name='%s'", bookname);
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(sql);
        System.out.println("删除成功！");
    }

    //归还图书，通过归还图书名称进行遍历查询，删除名称相同的整列信息
    public static void backbooks() throws SQLException, ClassNotFoundException {
        Statement stmt = conn.createStatement();
        String sql = "select * from library.books";
        ResultSet result = stmt.executeQuery(sql);
        System.out.println("请输入您要归还图书的名称");
        Scanner choice = new Scanner(System.in);
        String name2 = choice.next();
        while (result.next()) {
            int id = result.getInt(1);
            String name = result.getString(2);
            String author = result.getString(3);
            double price = result.getDouble(4);
            boolean areEqual = name2.equals(name);
            if (areEqual) {
                System.out.print("图书id:" + id + "\t");
                System.out.print("图书名称:" + name + "\t");
                System.out.print("图书作者:" + author + "\t");
                System.out.println("图书价格:" + price + "\t");
                System.out.println("请确认是否归还:\n1.是\n2.否");
                int choice2 = choice.nextInt();
                if (choice2 == 1) {
                    String Updatesql = String.format("UPDATE books SET ifborrowed ='yes' WHERE name ='%s'", name2);
                    PreparedStatement updateStatement = conn.prepareStatement(Updatesql);
                    updateStatement.executeUpdate();
                    System.out.println("成功归还！\n");
                } else
                    continue;
            }
        }
    }
    //更新功能，进行id或名称查询遍历实现信息对比，后重新录入信息进行信息修改并更新数据库
    public static void updatebooks() throws SQLException, ClassNotFoundException {
        Statement stmt = conn.createStatement();
        String sql = "select * from library.books";
        ResultSet result = stmt.executeQuery(sql);
        Scanner choice = new Scanner(System.in);
        System.out.println("请选择您要修改图书的查询方式：\n1.id查询\n2.名称查询\n");
        int choice3 = choice.nextInt();
        if (choice3 == 1) {
            System.out.println("请输入您想要修改图书的id:");
            int bookid = choice.nextInt();
            while (result.next()) {
                int id = result.getInt(1);
                if (bookid == id) {
                    String name = result.getString(2);
                    String author = result.getString(3);
                    double price = result.getDouble(4);
                    String ifborrowed = result.getString(5);
                    System.out.print("图书id:" + id + "\t");
                    System.out.print("图书名称:" + name + "\t");
                    System.out.print("图书作者:" + author + "\t");
                    System.out.print("图书价格:" + price + "\t");
                    System.out.println("是否在馆:" + ifborrowed + "\t");
                } else
                    continue;
                System.out.println("请输入更改的内容：");
                System.out.println("请输入修改后id:");
                int id2 = choice.nextInt();
                System.out.println("请输入修改后名称:");
                String  name2 = choice.next();
                System.out.println("请输入修改后作者:");
                String  author2 = choice.next();
                System.out.println("请输入修改后价格:");
                int price2 = choice.nextInt();
                System.out.println("请输入修改后在馆状态:");
                String  ifborrowed2 = choice.next();
                String Updatesql = String.format("UPDATE books SET id ='%s', name='%s', author='%s', price='%s',ifborrowed='%s'" +
                        " WHERE id ='%s'", id2, name2,author2,price2,ifborrowed2,id);
                PreparedStatement updateStatement = conn.prepareStatement(Updatesql);
                updateStatement.executeUpdate();
                System.out.println("成功修改！\n");
            }
        } else if (choice3 == 2) {
            System.out.println("请输入您想要查询的图书名称:");
            Scanner sc = new Scanner(System.in);
            String name1 = sc.next();
            while (result.next()) {
                int id = result.getInt(1);
                String name = result.getString(2);
                String author = result.getString(3);
                double price = result.getDouble(4);
                String ifborrowed = result.getString(5);
                boolean areEqual = name1.equals(name);
                if (areEqual) {
                    System.out.print("图书id:" + id + "\t");
                    System.out.print("图书名称:" + name + "\t");
                    System.out.print("图书作者:" + author + "\t");
                    System.out.print("图书价格:" + price + "\t");
                    System.out.println("是否在馆:" + ifborrowed + "\t\n");
                } else
                    continue;
                System.out.println("请输入修改的内容：");
                System.out.println("请输入修改后id:");
                int id2 = choice.nextInt();
                System.out.println("请输入修改后名称:");
                String  name2 = choice.next();
                System.out.println("请输入修改后作者:");
                String  author2 = choice.next();
                System.out.println("请输入修改后价格:");
                int price2 = choice.nextInt();
                System.out.println("请输入修改后在馆状态:");
                String  ifborrowed2 = choice.next();
                String Updatesql = String.format("UPDATE books SET id ='%s', name='%s', author='%s', price='%s',ifborrowed='%s'" +
                        " WHERE name ='%s'", id2, name2,author2,price2,ifborrowed2,name1);
                PreparedStatement updateStatement = conn.prepareStatement(Updatesql);
                updateStatement.executeUpdate();
                System.out.println("成功修改！\n");
            }
        }
    }
}
